<?php
ob_start();
session_start();
include_once($_SERVER['DOCUMENT_ROOT'] . '/htqlmba/configs/database.php');



if (PHP_SAPI == 'cli')
    die('This example should only be run from a Web Browser'); 

/** Include PHPExcel */
require_once($_SERVER['DOCUMENT_ROOT'] . '/htqlmba/libraries/PHPExcel_1.7.9_doc/Classes/PHPExcel.php');

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
        ->setLastModifiedBy("Maarten Balliauw")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");
		// Lay du lieu thong ke


	$madv = $_SESSION['madv'];
	$congsuat = $_SESSION['congsuat'];
	
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1', 'Công Ty Điện Lực Cà Mau');
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A2', 'Địa chỉ: 22 Ngô Quyền P2 TP Cà Mau');
	$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('B1', 'CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM');
	$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('B2', 'Độc Lập - Tự Do - Hạnh Phúc');
	$objPHPExcel->getActiveSheet()->mergeCells('B1:C1');
	$objPHPExcel->getActiveSheet()->mergeCells('B2:C2');
				$objPHPExcel->getActiveSheet()->getStyle('A1:C2')->applyFromArray(
						array(
							'font' => array(
								'size' => 13,
								'bold' => true
										)
							)
				);
				$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(
						array(
							'font' => array(
								'size' => 12,
								'italic' => true
	
							),
						)
				);
							$objPHPExcel->getActiveSheet()->getStyle('A1:C2')->applyFromArray(
						array(
							   'alignment' => array(
								'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
								'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
							)
						)
				);
	
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('C4', 'Ngày Tạo: ' . date('d/m/Y', time()));
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A4', 'Liên hệ: Nguyễn Phạm Thanh Tú/CBKT/Phòng KH-KT');
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A5', 'Điện thoại/Fax/Email: ');
	$objPHPExcel->getActiveSheet()->getStyle('A4:C5')->applyFromArray(
			array(
				'font' => array(
					'size' => 12,
				//'bold' => true
				),
				'alignment' => array(
					'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
				)
			)
	);
// Try lay du lieu vao bang excel truong hop 1 donvi va 1 congsuat
if ($madv != 'tatca') {
    $db = new database();
    $db->setQuery("select * from don_vi where MA_DON_VI = '" . $madv . "'");
    $re = $db->fetchAll();
    $row = mysql_fetch_array($re);
    $tendv = $row['TEN_DON_VI'];
    //Lay danh sach may don vi tung so huu		
    $db->setQuery("select * from chi_tiet_so_huu where MA_DON_VI = '" . $madv . "' group by SO_N0");
    $dong = $db->fetchAll();
    $mang = array();
    $i = 0;
    while ($in = mysql_fetch_array($dong)) {
        $mang[$i] = $in['SO_N0'];
        $i++;
    }
    // echo " <br >";
    $mang2 = array();
    $k = 0;
    for ($j = 0; $j < $i; $j++) {
        $db->setQuery("select * from chi_tiet_so_huu where SO_N0 = '" . $mang[$j] . "' order by NGAY_CHUYEN_DEN desc");
        $truyvan = $db->fetchAll();
        $madvtimdc = mysql_fetch_array($truyvan);
        if ($madvtimdc['MA_DON_VI'] == $madv) {
            $mang2[$k] = $madvtimdc['SO_N0'];
            $k++;
        }
    }

    if (count($mang2) == 0) {
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A6', 'Hiện tại đơn vị ' . $tendv . ' không sở hữu máy biến áp nào để thống kê');
    } else {
        if ($congsuat != 'tatca') {
            $dem = 0;
            $i = 0;
            for ($i = 0; $i < count($mang2); $i++) {
                $db->setQuery("select count(*) as soluong from may_bien_ap where SO_N0 = '" . $mang2[$i] . "' and CONG_SUAT = " . $congsuat);
                $truyvan = $db->fetchAll();
                while ($ketqua = mysql_fetch_array($truyvan)) {
                    if ($ketqua['soluong'] == 1)
                        $dem++;
                }
            }

            // bang cho 1 don vi va 1 cong suat
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A6', 'Bảng Thống Kê Theo Công Suất')
                    ->setCellValue('A7', 'Tên Đơn Vị')
                    ->setCellValue('B7', 'Công Suất (kVA)')
                    ->setCellValue('C7', 'Số Lượng')
                    ->setCellValue('A8', $tendv)
                    ->setCellValue('B8', $congsuat)
                    ->setCellValue('C8', $dem)
                    ->setCellValue('A9', 'Tổng số: ' . $dem . ' máy');
            // merge cho tieu de bang
            $objPHPExcel->getActiveSheet()->mergeCells('A6:C6');
            // mergecell cho dong tong so
            $objPHPExcel->getActiveSheet()->mergeCells('A9:C9');
            // set autoresize cho dong tieu de bang
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
            // mau` bang
            $objPHPExcel->getActiveSheet()->getStyle('A6:' .
                    $objPHPExcel->getActiveSheet()->getHighestColumn() .
                    $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
                    array(
                        'font' => array(
                            'size' => 12
                        ),
                        'borders' => array(
                            'allborders' => array(
                                'style' => PHPExcel_Style_Border::BORDER_THIN,
                                'color' => array('rgb' => '#000000'),
                            )
                        ),

                        'alignment' => array(
                            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                        )
                    )
            );
// Set format for tableheader
            $objPHPExcel->getActiveSheet()->getStyle('A6:C7')->applyFromArray(
                    array(
                        'font' => array(
                            'bold' => true,
                            'size' => 13
                        )
                    )
            );

            // set alignment cho dong tong so sau khi mergecell
            $objPHPExcel->getActiveSheet()->getStyle('A9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            // het bang		
        }

        //**&*&*&**&**&**&*&*&*&*&  TEST THEM 
        else {
            // Truong hop cong suat la tat ca - 1 don vi va tat ca cong suat

            $tongtungdv = 0;
            $cellcount = 8;
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A6', 'Bảng Thống Kê Theo Công Suất')
                    ->setCellValue('A7', 'Tên Đơn Vị')
                    ->setCellValue('B7', 'Công Suất (kVA)')
                    ->setCellValue('C7', 'Số Lượng')
                    ->setCellValue('A8', $tendv);
            $db->setQuery("select * from may_bien_ap  group by CONG_SUAT");
            $re = $db->fetchAll();
            while ($row = mysql_fetch_array($re)) {
                $cs = $row['CONG_SUAT'];
                $dem = 0;
                $i = 0;
                for ($i = 0; $i < count($mang2); $i++) {
                    $db->setQuery("select count(*) as soluong from may_bien_ap where SO_N0 = '" . $mang2[$i] . "' and CONG_SUAT = " . $cs);
                    $truyvan = $db->fetchAll();
                    while ($ketqua = mysql_fetch_array($truyvan)) {
                        if ($ketqua['soluong'] == 1)
                            $dem++;
                    }//het vong if
                }// het vong for

                if ($dem != 0) {
                    $tongtungdv = $tongtungdv + $dem;

                    $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('B' . $cellcount, $cs)
                            ->setCellValue('C' . $cellcount, $dem);
                    $cellcount++;
                }
            }//het vong while
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . $cellcount, 'Tổng số: ' . $tongtungdv . ' máy');
            $count_merge = $cellcount - 1;
            // merge cho tieu de bang
            $objPHPExcel->getActiveSheet()->mergeCells('A6:C6');
            // mergecell cho don vi
            $objPHPExcel->getActiveSheet()->mergeCells('A8:' . 'A' . $count_merge);
            // mergecell cho dong tong so
            $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':C' . $cellcount);
            // set autoresize cho dong tieu de bang
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);

            // set alignment 
            $objPHPExcel->getActiveSheet()->getStyle('A6:' .
                    $objPHPExcel->getActiveSheet()->getHighestColumn() .
                    $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
                    array(
                        'font' => array(
                            'size' => 12
                        ),
                        'borders' => array(
                            'allborders' => array(
                                'style' => PHPExcel_Style_Border::BORDER_THIN,
                                'color' => array('rgb' => '#000000'),
                            )
                        ),
                           'alignment' => array(
                            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                        )
                    )
            );

// Set format for tableheader
            $objPHPExcel->getActiveSheet()->getStyle('A6:C7')->applyFromArray(
                    array(
                        'font' => array(
                            'bold' => true,
                            'size' => 13
                        ),
                        'borders' => array(
                            'top' => array(
                                'style' => PHPExcel_Style_Border::BORDER_THIN
                            )
                        )
                    )
            );

            // set alignment cho dong tong so sau khi mergecell
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($cellcount))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($cellcount))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
// het phan dinh dang cho bang
        }// ket thuc else cua cong suat
    }
}// vong if cua 1 don vi
else {// else cua tat ca don vi
    $db1 = new database();
    $tongmaycacdv = 0;
    $count4merge = 8;


    $cellcount = 8;
    $count_merge = 8;

    $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A6', 'Bảng Thống Kê Theo Công Suất')
            ->setCellValue('A7', 'Tên Đơn Vị')
            ->setCellValue('B7', 'Công Suất (kVA)')
            ->setCellValue('C7', 'Số Lượng');

    $dsdv = "";
    $db1->setQuery("select * from don_vi as dv, tai_khoan as tk where dv.MA_DON_VI = tk.MA_DON_VI and  tk.QUYEN = 3");
    $re = $db1->fetchAll();
    while ($row = mysql_fetch_array($re)) {
        $tendv = $row['TEN_DON_VI'];
        $madv = $row['MA_DON_VI'];
        //Lay danh sach may don vi tung so huu		
        $db1->setQuery("select * from chi_tiet_so_huu where MA_DON_VI = '" . $madv . "' group by SO_N0");
        $dong = $db1->fetchAll();
        $mang = array();
        $i = 0;

        while ($in = mysql_fetch_array($dong)) {
            $mang[$i] = $in['SO_N0'];
            $i++;
        }
        $mang2 = array();
        $k = 0;

        for ($j = 0; $j < $i; $j++) {//for 
            $db1->setQuery("select * from chi_tiet_so_huu where SO_N0 = '" . $mang[$j] . "' 				order by 	NGAY_CHUYEN_DEN desc");
            $truyvan = $db1->fetchAll();
            $madvtimdc = mysql_fetch_array($truyvan);

            if ($madvtimdc['MA_DON_VI'] == $madv) {
                $mang2[$k] = $madvtimdc['SO_N0'];


                $k++;
            }
        }//for
        if (count($mang2) == 0)
            $dsdv = $dsdv . ", " . $tendv;

        else {//else count
            if ($congsuat != 'tatca') {

                $dem = 0;
                $i = 0;
                for ($i = 0; $i < count($mang2); $i++) {
                    $db1->setQuery("select count(*) as soluong from may_bien_ap where SO_N0 = '" . $mang2[$i] . "' and CONG_SUAT = " . $congsuat);
                    $truyvan = $db1->fetchAll();
                    while ($ketqua = mysql_fetch_array($truyvan)) {
                        if ($ketqua['soluong'] == 1)
                            $dem++;
                    }
                }

                $tongmaycacdv = $tongmaycacdv + $dem;
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, $tendv)
                        ->setCellValue('B' . $cellcount, $congsuat)
                        ->setCellValue('C' . $cellcount, $dem);
                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount . ':C' . $cellcount)->applyFromArray(
                        array(
                            'alignment' => array(
                                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            ),
                        )
                );
                $cellcount++;
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, 'Tổng số: ' . $dem . ' máy');
                $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':C' . $cellcount);
                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $cellcount++;
                // merge cho tieu de bang
            }


            else {
                // Truong hop cong suat la tat ca don vi & tat ca cong suat
                $tongtungdv = 0;
                // $cellcount=3;
                $count_merge = $cellcount;
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, $tendv);
                $db1->setQuery("select * from may_bien_ap  group by CONG_SUAT");
                $s = $db1->fetchAll();
                while ($r = mysql_fetch_array($s)) {

                    $cs = $r['CONG_SUAT'];
                    $dem = 0;
                    $i = 0;
                    for ($i = 0; $i < count($mang2); $i++) {
                        $db1->setQuery("select count(*) as soluong from may_bien_ap where SO_N0 = '" . $mang2[$i] . "' and CONG_SUAT = " . $cs);
                        $t = $db1->fetchAll();
                        while ($q = mysql_fetch_array($t)) {
                            if ($q['soluong'] == 1)
                                $dem++;
                        }//het vong if
                    }// het vong for

                    if ($dem != 0) {
                        $tongmaycacdv = $tongmaycacdv + $dem;
                        $tongtungdv = $tongtungdv + $dem;
                        $objPHPExcel->setActiveSheetIndex(0)
                                ->setCellValue('B' . $cellcount, $cs)
                                ->setCellValue('C' . $cellcount, $dem);
                        $cellcount++;
                    }
                }//het vong while
                // mergecell cho don vi
                // mergecell cho dong tong so
                // try - merge ten dv			


                $cellcount_tmp = $cellcount - 1;

                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, 'Tổng số: ' . $tongtungdv . ' máy ');

                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $objPHPExcel->getActiveSheet()->mergeCells('A' . $count4merge . ':A' . $cellcount_tmp);
                // Set format for tableheader
                $objPHPExcel->getActiveSheet()->getStyle('A' . $count4merge . ':C' . $cellcount_tmp)->applyFromArray(
                        array(
                            'alignment' => array(
                                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            )
							)
                 );

                $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':C' . $cellcount);
                $count4merge = $cellcount + 1;
                $cellcount++;
            }// ket thuc else cua cong suat
        }//else count
    }

    $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A' . $cellcount, 'Tổng số tất cả các máy theo điều kiện trên: ' . $tongmaycacdv . ' máy');



    //merg dau` bang
    $objPHPExcel->getActiveSheet()->mergeCells('A6:C6');
    //merge tong so tat ca
    $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':C' . $cellcount);
    // set autoresize cho dong tieu de bang
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);

    // mau` bang
    $objPHPExcel->getActiveSheet()->getStyle('A6:' .
            $objPHPExcel->getActiveSheet()->getHighestColumn() .
            $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
            array(
                'font' => array(
                    'size' => 12,

                ),
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN,
                        'color' => array('rgb' => '#000000'),
                    )
                )
            )
    );
// Set format for tableheader
    $objPHPExcel->getActiveSheet()->getStyle('A6:C7')->applyFromArray(
            array(
                'font' => array(
                    'bold' => true,
                    'size' => 13
                ),
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                )

            )
    );


    if ($dsdv != "") {
        $cellcount = $cellcount + 1;
        $dsdv = ltrim($dsdv, ",");
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A' . $cellcount, 'Danh sách các đơn vị không sở hữu MBA: ' . $dsdv);
        $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':I' . $cellcount);
        $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->applyFromArray(array('font' => array('name' => 'Times new Roman', 'bold' => true, 'italic' => true, 'size' => 12)));

        //$objPHPExcel->setActiveSheetIndex(0)
        //			->setCellValue('A'.($cellcount + 1),'Danh sách các đơn vị không sở hữu MBA: //'.date('d/m/Y', time()));
    }
}
		// dinh font cho toan bo file
   		 $objPHPExcel->getActiveSheet()->getStyle('A1:' .
            $objPHPExcel->getActiveSheet()->getHighestColumn() .
            $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
            array(
                'font' => array(                    
                    'name' => 'Times new Roman'
                )

            )
    );

// &*&*&**&* HET TEST THEM
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Thống kê theo công suất');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="thongkecongsuat.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>